package com.mbbmap.security.manager;

import java.util.*;
import java.sql.*;

import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.app.manager.SystemLogManager;
import com.mbbmap.security.dao.SecAccessDao;
import com.mbbmap.security.dao.SecGroupLevelDao;
import com.mbbmap.security.dao.SecGroupsDao;
import com.mbbmap.security.dao.SecUserDao;
import com.mbbmap.util.*;

public class SecurityListManager {
	private static SecurityListManager instance = null;

		/**
		 * Returns the single instance, creating one if it's the
		 * first time this method is called.
		 *
		 * @return IslamicContractHome. The single instance.
		 */
	public static SecurityListManager getInstance() {
		//thread-safe implementation
		if (instance == null) {
			synchronized (SecurityListManager.class) {
				if (instance == null) {
					instance = new SecurityListManager();
				}
			}
		}
		return instance;
	}

	private SecurityListManager() {
		super();
	}


	/**
	 *	Get Group List
	 */
	public ArrayList findSecGroupList(String strMerchant) {

		String querySql = null;
		ArrayList secgroupList = new ArrayList();

		int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		String connectionPool;
		SystemLogManager sysLogMgr;

		connectionPool = ConfigManager.getInstance().getConnectionPool();
		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		sysLogMgr = SystemLogManager.getInstance();

		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

		PreparedStatement stmt = null;
		ResultSet rs= null;

		try {
			if (conn == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("SecSecurityListHome_findSecGroupList_001 [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			} //if (conn == null)
			else{

				StringBuffer sqlStmt = new StringBuffer("");

				querySql = "select role_code,role_name,created_by, created_date from tblaccessrole where mer_name = '" + strMerchant
							+ "' order by created_date";
				sqlStmt.append(querySql);


				stmt = conn.prepareStatement(sqlStmt.toString());

				rs = stmt.executeQuery();

				while (rs.next()){

						SecGroupsDao secgroupsDao = new SecGroupsDao();

						String strgroupCode = rs.getString(1);
						if (strgroupCode != null){
							strgroupCode = strgroupCode.trim();
						}
						String strgroupName = rs.getString(2);
						if (strgroupName != null){
							strgroupName = strgroupName.trim();
						}
						String strcreatedby = rs.getString(3);
						if (strcreatedby != null){
							strcreatedby = strcreatedby.trim();
						}
						java.util.Date dCreateddt = rs.getDate(4);
						String strcreateddt = "";
						if (dCreateddt != null){
						    strcreateddt = DateUtil.formatLocalSlash(dCreateddt).trim();
						}

						secgroupsDao.set(strgroupCode,strgroupName,strcreatedby,strcreateddt);
						secgroupList.add(secgroupsDao);
				} //while (rs.next())
				rs.close();
				stmt.close();
			} //[ELSE] if (conn == null)
		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("TradeAcctHome_findTradeAcct_002 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("TradeAcctHome_findTradeAcct_003 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}

		return secgroupList;
	}

	/**
	 * Get Group Level List
	 */
	public ArrayList findSecGroupLevelList(String strMerchant, ArrayList arlSecGroupList) {

		ArrayList secGroupLevelList = new ArrayList();

		int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		String connectionPool;
		SystemLogManager sysLogMgr;

		connectionPool = ConfigManager.getInstance().getConnectionPool();
		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		sysLogMgr = SystemLogManager.getInstance();

		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

		PreparedStatement stmt = null;
		ResultSet rs= null;

		try {
			if (conn == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("SecSecurityListHome_findSecGroupLevelList_001 [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			} //if (conn == null)
			else{

				for(int i = 0; i < arlSecGroupList.size(); i++)
				{
					StringBuffer sqlStmt = new StringBuffer("");

					SecGroupsDao secgroupsDao = new SecGroupsDao();

					secgroupsDao = (SecGroupsDao) arlSecGroupList.get(i);

					sqlStmt.append("select a.parent_id,a.child_id,a.created_by, a.created_date, b.role_name from TBLTREE a inner join tblaccessrole b on a.child_id = b.role_code where a.mer_name = '"+strMerchant+"' AND a.parent_id = '" + secgroupsDao.getGroupCode() + "' AND a.tree_type ='" + Constants.TREE_TYPE_ACCESS + "'");
					//System.out.println("SecSecurityListHome_findSecGroupLevelList_002 : " + sqlStmt);

					stmt = conn.prepareStatement(sqlStmt.toString());

					rs = stmt.executeQuery();

					while (rs.next()){

							SecGroupLevelDao secGroupLevelDao = new SecGroupLevelDao();

							String sgroupCode = rs.getString(1);
							if (sgroupCode != null){
								sgroupCode = sgroupCode.trim();
							}
							String sgroupLevel = rs.getString(2);
							if (sgroupLevel != null){
								sgroupLevel = sgroupLevel.trim();
							}
							String screatedby = rs.getString(3);
							if (screatedby != null){
								screatedby = screatedby.trim();
							}
							java.util.Date dCreateddt = rs.getDate(4);
							String screateddt = "";
							if (dCreateddt != null){
								screateddt = DateUtil.formatLocalSlash(dCreateddt).trim();
							}
							String cgroupName = rs.getString(5);
							if (cgroupName != null){
								cgroupName = cgroupName.trim();
							}
							secGroupLevelDao.setChildGroupName(cgroupName);
							secGroupLevelDao.set(sgroupCode,sgroupLevel,screatedby,screateddt);
							secGroupLevelList.add(secGroupLevelDao);
					} //while (rs.next())
					rs.close();
					stmt.close();
				}

			} //[ELSE] if (conn == null)
		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("SecSecurityListHome_findTradeAcct_002 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("SecSecurityListHome_findTradeAcct_003 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}

		return secGroupLevelList;
	}

	/**
	 *  Get Group Access List
	 */
	public ArrayList findSecGroupAccessList(String strMerchant, ArrayList arlSecGroupList) {

		ArrayList secGroupAccessList = new ArrayList();

		int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		String connectionPool;
		SystemLogManager sysLogMgr;

		connectionPool = ConfigManager.getInstance().getConnectionPool();
		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		sysLogMgr = SystemLogManager.getInstance();

		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

		PreparedStatement stmt = null;
		ResultSet rs= null;

		try {
			if (conn == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("SecSecurityListHome_findSecGroupAccessList_001 [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			} //if (conn == null)
			else{
				for(int i = 0; i < arlSecGroupList.size(); i++)
				{
					StringBuffer sqlStmt = new StringBuffer("");

					SecGroupsDao secgroupsDao = new SecGroupsDao();

					secgroupsDao = (SecGroupsDao) arlSecGroupList.get(i);

					sqlStmt.append("select a.role_code,a.module_code, a.read_flag,a.update_flag,a.write_flag,a.created_by, a.created_date, b.module_name from TBLROLEPERMISSION a inner join tblaccessmodule b on b.module_code = a.module_code  where a.mer_name = '"+strMerchant+"' AND a.role_code ='" + secgroupsDao.getGroupCode() + "'ORDER by a.module_code");
					//System.out.println("SecSecurityListHome_findSecGroupAccessList_002 : " + sqlStmt);

					stmt = conn.prepareStatement(sqlStmt.toString());
					rs = stmt.executeQuery();

					while (rs.next()){
							SecAccessDao secaccessDao = new SecAccessDao();
							String sgroupCode = rs.getString(1);
							if (sgroupCode != null){
								sgroupCode = sgroupCode.trim();
							}
							String smoduleCode = rs.getString(2);
							if (smoduleCode != null){
								smoduleCode = smoduleCode.trim();
							}
							String sread_flag = rs.getString(3);
							if (sread_flag != null){
								sread_flag = sread_flag.trim();
							}
							String supdate_flag = rs.getString(4);
							if (supdate_flag != null){
								supdate_flag = supdate_flag.trim();
							}
							String swrite_flag = rs.getString(5);
							if (swrite_flag != null){
								swrite_flag = swrite_flag.trim();
							}
							String screatedby = rs.getString(6);
							if (screatedby != null){
								screatedby = screatedby.trim();
							}
							java.util.Date dCreateddt = rs.getDate(7);
							String screateddt = "";
							if (dCreateddt != null){
								screateddt = DateUtil.formatLocalSlash(dCreateddt).trim();
							}
							String smoduleName = rs.getString(8);
							if (smoduleName != null){
								smoduleName = smoduleName.trim();
							}

							secaccessDao.set(sgroupCode,smoduleCode,sread_flag,supdate_flag,swrite_flag,screatedby,screateddt);
							secaccessDao.setModuleName(smoduleName);
							secGroupAccessList.add(secaccessDao);
					} //while (rs.next())
					rs.close();
					stmt.close();
				}
			} //[ELSE] if (conn == null)
		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("SecSecurityListHome_findTradeAcct_002 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("SecSecurityListHome_findTradeAcct_003 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}

				//System.out.println("Resultset12 . next()");
		return secGroupAccessList;
	}

	/**
	 *  Get Group User List
	 */
	public ArrayList findSecGroupUserList(String strMerchant, ArrayList arlSecSubGroupList) {

		ArrayList secGroupUserList = new ArrayList();

		int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		String connectionPool;
		SystemLogManager sysLogMgr;

		connectionPool = ConfigManager.getInstance().getConnectionPool();
		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		sysLogMgr = SystemLogManager.getInstance();

		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

		PreparedStatement stmt = null;
		ResultSet rs= null;
		PreparedStatement stmt2 = null;
		ResultSet rs2= null;

		try {
			if (conn == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("SecSecurityListHome_findSecGroupUserList_001 [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			} //if (conn == null)
			else{

					for(int i = 0; i < arlSecSubGroupList.size(); i++)
					{
						StringBuffer sqlStmt = new StringBuffer("");

						SecGroupsDao secGroupDao1 = new SecGroupsDao();

						secGroupDao1 = (SecGroupsDao) arlSecSubGroupList.get(i);

						StringBuffer sqlStmt2 = new StringBuffer("");
						sqlStmt2.append("select login_id, name, to_char(last_login_timestamp,'dd/mm/yyyy hh24:mi') from " + Constants.DB_TBL_USER + "  where mer_name = '" + strMerchant + "' and role_code = '" + secGroupDao1.getGroupCode() +"' ");
						//sqlStmt2.append("select a.logonid, d.fullname from bguser a inner join (property b inner join propertydefinition c on (b.idx = c.idx) and (c.uname = '" + Constants.CSR_ROLE + "') and (b.value = '" + sgroupLevel + "')) on a.id = b.id left join name d on a.nameid = d.id");
						System.out.println("SecUsersHome_findSecGroupUserList_003 : " + sqlStmt2);

						stmt2 = conn.prepareStatement(sqlStmt2.toString());

						rs2 = stmt2.executeQuery();

						String sLoginID = "";
						String sUserName = "";
						String sLastLogin = "";
						while (rs2.next()){
							SecUserDao secuserdao = new SecUserDao();

							sLoginID = "";
							sUserName = "";
							sLastLogin = "";
							
							sLoginID = rs2.getString(1);
							sUserName = rs2.getString(2);
							sLastLogin = rs2.getString(3);
							
							
							//System.out.println("SecUsersHome_findSecGroupUserList_003 : sLoginID " + sLoginID);
							secuserdao.set(sLoginID,secGroupDao1.getGroupCode(),"","");
							secuserdao.setPGroupname(secGroupDao1.getGroupName());
							secuserdao.setUserName(sUserName);
							secuserdao.setLastLoginDateTime(sLastLogin);
							secGroupUserList.add(secuserdao);
						}
						stmt2.close();
						rs2.close();
					}
			} //[ELSE] if (conn == null)
		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("SecSecurityListHome_findSecGroupUserList_002 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("SecSecurityListHome_findSecGroupUserList_003 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}
		return secGroupUserList;
	}
}
